Imports System.Data.SqlClient
Imports System.Web.Mail
Imports Secure.Functions
Imports Secure.PublicFunctions

Public Class EmailNCAC
    Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub
    Protected WithEvents Label1 As System.Web.UI.WebControls.Label
    Protected WithEvents Label2 As System.Web.UI.WebControls.Label
    Protected WithEvents txtFrom As System.Web.UI.WebControls.TextBox
    Protected WithEvents Label4 As System.Web.UI.WebControls.Label
    Protected WithEvents RequiredFieldValidator1 As System.Web.UI.WebControls.RequiredFieldValidator
    Protected WithEvents Label3 As System.Web.UI.WebControls.Label
    Protected WithEvents txtSubject As System.Web.UI.WebControls.TextBox
    Protected WithEvents RequiredFieldValidator2 As System.Web.UI.WebControls.RequiredFieldValidator
    Protected WithEvents RequiredFieldValidator3 As System.Web.UI.WebControls.RequiredFieldValidator
    Protected WithEvents ValidationSummary1 As System.Web.UI.WebControls.ValidationSummary
    Protected WithEvents txtAffiliate As System.Web.UI.WebControls.TextBox
    Protected WithEvents Label5 As System.Web.UI.WebControls.Label
    Protected WithEvents ddl_Provinces As System.Web.UI.WebControls.DropDownList
    Protected WithEvents btnCancel As System.Web.UI.WebControls.Button
    Protected WithEvents btnSend As System.Web.UI.WebControls.Button
    Protected WithEvents ftbMessage As FreeTextBoxControls.FreeTextBox
    Protected WithEvents Label6 As System.Web.UI.WebControls.Label
    Protected WithEvents ddlCourseType As System.Web.UI.WebControls.DropDownList
    Protected WithEvents Label7 As System.Web.UI.WebControls.Label
    Protected WithEvents ddlTrainerType As System.Web.UI.WebControls.DropDownList
    Protected WithEvents chk_Course As System.Web.UI.WebControls.CheckBox
    Protected WithEvents chk_Trainer As System.Web.UI.WebControls.CheckBox

    'NOTE: The following placeholder declaration is required by the Web Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        'only allow Moderators or Admins to view
        'If User.IsInRole(ConfigurationManager.AppSettings.Item("RoleModerator")) Or _
        '    User.IsInRole(ConfigurationManager.AppSettings.Item("RoleAdmin")) Then
        'Else
        '    Response.Redirect("")
        'End If

        btnCancel.CausesValidation = False

        If Not IsPostBack Then
            BindEmailDDL()
        End If
        'Response.Write(ddlEmailMembers.SelectedValue.ToString)

    End Sub

    Private Sub BindEmailDDL()
        ddlCourseType.DataSource = Create_Course_Type_Array()
        ddlCourseType.DataTextField = "description"
        ddlCourseType.DataValueField = "id"
        ddlCourseType.DataBind()

        ddlTrainerType.DataSource = Create_Trainer_Type_Array()
        ddlTrainerType.DataTextField = "description"
        ddlTrainerType.DataValueField = "id"
        ddlTrainerType.DataBind()

        'todo: uncomment
        'ddl_Provinces.DataSource = Return_Affiliates
        ddl_Provinces.DataTextField = "Province"
        ddl_Provinces.DataValueField = "Affiliate_ID"
        ddl_Provinces.DataBind()

        Dim li As ListItem
        li = New ListItem

        li.Text = "All"
        li.Value = "0"

        ddl_Provinces.Items.Insert(0, li)
    End Sub

    Private Sub SendUserEmail()
        'Response.Write(strHTMLFormatted)
        Dim objMail As New MailMessage

        'bad words
        'your, payment, questions, address, email, city

        'get the email addresses to send the message to
        GetMemberEmailAddresses()

        'objMail.Fields("http://schemas.microsoft.com/cdo/configuration/smtsperver") = "vmail.accesscomm.ca" 'vmail.accesscomm.ca
        'objMail.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
        'objMail.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        'objMail.Fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        'objMail.Fields("http://schemas.microsoft.com/cdo/configuration/sendusername") = "gary.pronych" '"tristan666" '
        'objMail.Fields("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "garyrhd"

        objMail.From = txtFrom.Text
        objMail.To = txtFrom.Text
        objMail.Bcc = "gary.pronych@gmail.com;" & GetMemberEmailAddresses()
        objMail.Subject = SqlReplaceText(txtSubject.Text)
        objMail.Body = HttpUtility.HtmlEncode(SqlReplaceText(ftbMessage.Text))
        objMail.BodyFormat = MailFormat.Html
        SmtpMail.SmtpServer = "localhost"
        'SmtpMail.SmtpServer = "vmail.accesscomm.ca" 'mailout.accesscomm.ca

        SmtpMail.Send(objMail)

        'Response.Write("email will be sent to" & txtAffiliate.Text())
        'Response.Write(GetMemberEmailAddresses)

    End Sub


    Public Function GetMemberEmailAddresses() As String
        Dim strsql As String
        Dim sqlReaderTo As SqlDataReader
        Dim strarray As String = ""

        Dim objConn As SqlConnection = New SqlConnection(ConfigurationManager.AppSettings.Item("SQLConnectionString"))
        Dim cmdGetEmail As SqlCommand

        'strsql = ddlEmailMembers.SelectedValue.ToString
        strsql = "Select * from members where (account_status = 3) "

        Select Case ddl_Provinces.SelectedItem.Value
            Case "0"
                strsql &= "and 1 = 1 "
            Case Else
                strsql &= "and affiliate_id = '" & ddl_Provinces.SelectedItem.Value & "' "
        End Select

        Dim strTrainerType As String

        Select Case ddlTrainerType.SelectedItem.Value
            Case "0"
                'none
            Case "1"
                strTrainerType = "Instructor"
                strsql &= " and (CATN_Instructor = 'Y' or TNCC_Instructor = 'Y' or ENPC_Instructor = 'Y')"
            Case "2"
                strTrainerType = "Trainer"
                strsql &= " and (ENPC_Provider = 'Y' or TNCC_Provider = 'Y' or CATN_Provider = 'Y')"
            Case "3"
                strTrainerType = "Director"
                strsql &= " and (CATN_Course_Trainer = 'Y' or TNCC_Course_Trainer = 'Y' or ENPC_Course_Trainer = 'Y')"
            Case "4"
                strTrainerType = "All"
                strsql &= " and (TNCC_Provider = 'Y' or TNCC_Instructor = 'Y' or TNCC_Course_Trainer = 'Y'"
                strsql &= " or CATN_Provider = 'Y' or CATN_Instructor = 'Y' or CATN_Course_Trainer = 'Y'"
                strsql &= " or ENPC_Provider = 'Y' or ENPC_Instructor = 'Y' or ENPC_Course_Trainer = 'Y')"

        End Select

        Dim strCourseType As String

        Select Case ddlCourseType.SelectedItem.Value
            Case "0"
                'none
            Case "1"
                strCourseType = "ENPC"
                strsql &= "and (ENPC_Provider = 'Y' or ENPC_Instructor = 'Y' or ENPC_Course_Trainer = 'Y')"
            Case "2"
                strCourseType = "TNCC"
                strsql &= " and (TNCC_Provider = 'Y' or TNCC_Instructor = 'Y' or TNCC_Course_Trainer = 'Y')"
            Case "3"
                strCourseType = "CATN"
                strsql &= " and (CATN_Provider = 'Y' or CATN_Instructor = 'Y' or CATN_Course_Trainer = 'Y')"
            Case "4"
                strCourseType = "All"
                strsql &= " and (TNCC_Provider = 'Y' or TNCC_Instructor = 'Y' or TNCC_Course_Trainer = 'Y'"
                strsql &= " or CATN_Provider = 'Y' or CATN_Instructor = 'Y' or CATN_Course_Trainer = 'Y'"
                strsql &= " or ENPC_Provider = 'Y' or ENPC_Instructor = 'Y' or ENPC_Course_Trainer = 'Y')"

        End Select

        cmdGetEmail = New SqlCommand(strsql, objConn)
        cmdGetEmail.CommandType = CommandType.Text
        objConn.Open()
        sqlReaderTo = cmdGetEmail.ExecuteReader(CommandBehavior.CloseConnection)

        Dim intcount As Integer = 0

        While sqlReaderTo.Read()
            strarray += sqlReaderTo("emailaddress").ToString
            strarray += "; "
            intcount += 1
        End While

        sqlReaderTo.Close()
        'txtAffiliate.Text = strarray
        'Response.Write(intcount)
        Return (strarray)
        'Return (intcount)

    End Function

    Private Sub btnSend_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSend.Click

        SendUserEmail()
        Response.Redirect("default.aspx")
        Response.Write("email sent")

    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

        Response.Redirect("default.aspx")

    End Sub

    Private Function Create_Trainer_Type_Array() As DataTable
        Dim dt As New DataTable
        Dim dr As DataRow

        dt.Columns.Add(New DataColumn("ID", GetType(String)))
        dt.Columns.Add(New DataColumn("Description", GetType(String)))

        dr = dt.NewRow()
        dr(0) = "0"
        dr(1) = "None"
        dt.Rows.Add(dr)
        dr = dt.NewRow()
        dr(0) = "1"
        dr(1) = "Trainers"
        dt.Rows.Add(dr)
        dr = dt.NewRow()
        dr(0) = "2"
        dr(1) = "Instructors"
        dt.Rows.Add(dr)
        dr = dt.NewRow()
        dr(0) = "3"
        dr(1) = "Instructor Trainers"
        dt.Rows.Add(dr)
        dr = dt.NewRow()
        dr(0) = "4"
        dr(1) = "All"
        dt.Rows.Add(dr)

        Return dt

    End Function

    Private Function Create_Course_Type_Array() As DataTable
        Dim dt As New DataTable
        Dim dr As DataRow

        dt.Columns.Add(New DataColumn("ID", GetType(String)))
        dt.Columns.Add(New DataColumn("Description", GetType(String)))

        dr = dt.NewRow()
        dr(0) = "0"
        dr(1) = "None"
        dt.Rows.Add(dr)
        dr = dt.NewRow()
        dr(0) = "1"
        dr(1) = "ENPC"
        dt.Rows.Add(dr)
        dr = dt.NewRow()
        dr(0) = "2"
        dr(1) = "TNCC"
        dt.Rows.Add(dr)
        dr = dt.NewRow()
        dr(0) = "3"
        dr(1) = "CATN"
        dt.Rows.Add(dr)
        dr = dt.NewRow()
        dr(0) = "4"
        dr(1) = "All"
        dt.Rows.Add(dr)

        Return dt

    End Function


#Region " Verifying Affiliate ID "

    Public Sub VerifyAffiliate(ByVal strAffiliateID As String)
        Select Case strAffiliateID
            Case 1
                If User.IsInRole(ConfigurationManager.AppSettings.Item("RoleModerator")) Or _
                    User.IsInRole(ConfigurationManager.AppSettings.Item("RoleAdmin")) Or _
                    User.IsInRole(ConfigurationManager.AppSettings.Item("RoleBC")) Then
                    txtAffiliate.Text = ConfigurationManager.AppSettings.Item("RoleBC")
                Else
                    Response.Redirect("")
                End If
            Case 2
                If User.IsInRole(ConfigurationManager.AppSettings.Item("RoleModerator")) Or _
                    User.IsInRole(ConfigurationManager.AppSettings.Item("RoleAdmin")) Or _
                    User.IsInRole(ConfigurationManager.AppSettings.Item("RoleAB")) Then
                    txtAffiliate.Text = ConfigurationManager.AppSettings.Item("RoleAB")
                Else
                    Response.Redirect("")
                End If
            Case 3
                If User.IsInRole(ConfigurationManager.AppSettings.Item("RoleModerator")) Or _
                    User.IsInRole(ConfigurationManager.AppSettings.Item("RoleAdmin")) Or _
                    User.IsInRole(ConfigurationManager.AppSettings.Item("RoleSK")) Then
                    txtAffiliate.Text = ConfigurationManager.AppSettings.Item("RoleSK")
                Else
                    Response.Redirect("")
                End If
            Case 4
                If User.IsInRole(ConfigurationManager.AppSettings.Item("RoleModerator")) Or _
                    User.IsInRole(ConfigurationManager.AppSettings.Item("RoleAdmin")) Or _
                    User.IsInRole(ConfigurationManager.AppSettings.Item("RoleMB")) Then
                    txtAffiliate.Text = ConfigurationManager.AppSettings.Item("RoleMB")
                Else
                    Response.Redirect("")
                End If
            Case 5
                If User.IsInRole(ConfigurationManager.AppSettings.Item("RoleModerator")) Or _
                    User.IsInRole(ConfigurationManager.AppSettings.Item("RoleAdmin")) Or _
                    User.IsInRole(ConfigurationManager.AppSettings.Item("RoleONT")) Then
                    txtAffiliate.Text = ConfigurationManager.AppSettings.Item("RoleONT")
                Else
                    Response.Redirect("")
                End If
            Case 6
                If User.IsInRole(ConfigurationManager.AppSettings.Item("RoleModerator")) Or _
                    User.IsInRole(ConfigurationManager.AppSettings.Item("RoleAdmin")) Or _
                    User.IsInRole(ConfigurationManager.AppSettings.Item("RoleNB")) Then
                    txtAffiliate.Text = ConfigurationManager.AppSettings.Item("RoleNB")
                Else
                    Response.Redirect("")
                End If
            Case 7
                If User.IsInRole(ConfigurationManager.AppSettings.Item("RoleModerator")) Or _
                    User.IsInRole(ConfigurationManager.AppSettings.Item("RoleAdmin")) Or _
                    User.IsInRole(ConfigurationManager.AppSettings.Item("RoleNS")) Then
                    txtAffiliate.Text = ConfigurationManager.AppSettings.Item("RoleNS")
                Else
                    Response.Redirect("")
                End If
            Case 8
                If User.IsInRole(ConfigurationManager.AppSettings.Item("RoleModerator")) Or _
                    User.IsInRole(ConfigurationManager.AppSettings.Item("RoleAdmin")) Or _
                    User.IsInRole(ConfigurationManager.AppSettings.Item("RolePEI")) Then
                    txtAffiliate.Text = ConfigurationManager.AppSettings.Item("RolePEI")
                Else
                    Response.Redirect("")
                End If
            Case 9
                If User.IsInRole(ConfigurationManager.AppSettings.Item("RoleModerator")) Or _
                    User.IsInRole(ConfigurationManager.AppSettings.Item("RoleAdmin")) Or _
                    User.IsInRole(ConfigurationManager.AppSettings.Item("RoleNF")) Then
                    txtAffiliate.Text = ConfigurationManager.AppSettings.Item("RoleNF")
                Else
                    Response.Redirect("")
                End If
            Case 10
                If User.IsInRole(ConfigurationManager.AppSettings.Item("RoleModerator")) Or _
                    User.IsInRole(ConfigurationManager.AppSettings.Item("RoleAdmin")) Or _
                    User.IsInRole(ConfigurationManager.AppSettings.Item("RoleOTHER")) Then
                    txtAffiliate.Text = ConfigurationManager.AppSettings.Item("RoleOTHER")
                Else
                    Response.Redirect("")
                End If
        End Select
    End Sub
#End Region

End Class
